/**
 * 
 */
package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;


/**
 * @author Lindgren
 *
 */
public class DBProduct implements IFDBProduct{

	private  Connection con;
    /** Creates a new instance of DBProduct */
    public DBProduct() {
      con = DbConnection1.getInstance().getDBcon();
    }

	public ArrayList<Product> getAllProducts(boolean retrieveAssociation) {
		//  Auto-generated method stub
		return miscWhere("", retrieveAssociation);
	}

	public Product findProduct(int prodID, boolean retrieveAssociation) {
		//  Auto-generated method stub
		String wClause = "  productID = '" + prodID + "'";
        return singleWhere(wClause, retrieveAssociation);
	}

	public Product searchProductName(String name, boolean retrieveAssociation) {
		//  Auto-generated method stub
		String wClause = "prodname like '%" + name + "%'";
        System.out.println("SearchProduct " + wClause);
        return singleWhere(wClause, retrieveAssociation);
	}

	public Product searchProductDescription(String prodDescription, boolean retrieveAssociation) {
		//  Auto-generated method stub
		String wClause = "productDescription like '%" + prodDescription + "%'";
        System.out.println("SearchProduct " + wClause);
        return singleWhere(wClause, retrieveAssociation);
	}
	
	public int insertClothing(Clothing clo) throws Exception {
		//  Auto-generated method stub
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(productID) from product");
        nextID = nextID + 1;
        System.out.println("next productID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO product(productID, prodName, purchasePrice, salesPrice, rentPrice, " +
	   		"CountryOfOrigin, productRangeID, productDescription, clothingSize, ClothingColor," +
	   		" equipmentType, GunFabricator, GunCalibre, prodSupID)  VALUES('"+
	  		nextID + "','" +
			clo.getName()  + "','"  +
		    clo.getPurchasePrice() + "','"  +
            clo.getSalesPrice() + "','"  +
		    clo.getRentPrice() + "','"  +
            clo.getCountryOfOrigin() + "','"  +
		    clo.getRange().getRangeID() + "','"  +
            clo.getDescription() + "','"  +
		    clo.getClothingSize() + "','"  +
            clo.getClothingColor() + "','"  +
		    null + "','"  +
            null + "','"  +
		    null + "','"  +
            clo.getSupplier().getSupplierId() + "')";

       System.out.println("insert : " + query);
       
       //////////////fra DBEmployee ///////////////////
//       try{ // insert new employee +  dependent
//           Statement stmt = con.createStatement();
//           stmt.setQueryTimeout(5);
//      	  rc = stmt.executeUpdate(query);
//           stmt.close();
//       }//end try
//     //////////fra DBEmployee ///////////////////
       
      try{ // insert new clothing
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Clothing ikke oprettet");
          throw new Exception ("product is not inserted correct");
       }
       return(rc);
	}

	public int updateClothing(Clothing clo) {
		//  Auto-generated method stub
		Clothing cloObj  = clo;
		int rc=-1;
		
		String query="UPDATE product SET "+
				"prodName ='"+ cloObj.getName()+"', "+
				
				"purchasePrice ='"+cloObj.getPurchasePrice()+"', "+
				"salesPrice ='"+cloObj.getSalesPrice()+"', "+
				"rentPrice  ='"+cloObj.getRentPrice()+"', "+
				"CountryOfOrigin ='"+cloObj.getCountryOfOrigin()+"', "+
				"productRangeID ='"+cloObj.getRange().getRangeID()+"', "+
				"productDescription ='"+cloObj.getDescription()+"', "+
				"clothingSize ='"+cloObj.getClothingSize()+"', "+
				"ClothingColor ='"+cloObj.getClothingColor()+"', "+
				"equipmentType  ='"+null+"', "+
				"GunFabricator ='"+null+"', "+
				"GunCalibre  ='"+null+"', "+
				"prodSupID ='"+cloObj.getSupplier().getSupplierId()+"' "+
		 	  
		          " WHERE productID = '"+ cloObj.getProductId() + "'";
		
		
                System.out.println("Update query:" + query);
  		try{ // update clothing
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}

	public int insertEquipment(Equipment equ) throws Exception {
		//  Auto-generated method stub
		int nextID = GetMax.getMaxId("Select max(productID) from product");
        nextID = nextID + 1;
        System.out.println("next productID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO product(productID, prodName, purchasePrice, salesPrice, rentPrice, CountryOfOrigin, productRangeID, productDescription, clothingSize, ClothingColor, equipmentType, GunFabricator, GunCalibre, prodSupID)  VALUES('"+
	  		nextID + "','" +
	  		equ.getName()  + "','"  +
	  		equ.getPurchasePrice() + "','"  +
	  		equ.getSalesPrice() + "','"  +
	  		equ.getRentPrice() + "','"  +
	  		equ.getCountryOfOrigin() + "','"  +
	  		equ.getRange().getRangeID() + "','"  +
	  		equ.getDescription() + "','"  +
	  		null + "','"  +
	  		null + "','"  +
		    equ.getEquipmentType() + "','"  +
            null + "','"  +
		    null + "','"  +
		    equ.getSupplier().getSupplierId() + "')";

       System.out.println("insert : " + query);
      try{ // insert new equipment
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Clothing ikke oprettet");
          throw new Exception ("Clothing is not inserted correct");
       }
       return(rc);
	}

	public int updateEquipment(Equipment equ) {
		//  Auto-generated method stub
		Equipment equObj  = equ;
		int rc=-1;
		
		String query="UPDATE product SET "+
				"prodName ='"+ equObj.getName()+"', "+
				
				"purchasePrice ='"+equObj.getPurchasePrice()+"', "+
				"salesPrice ='"+equObj.getSalesPrice()+"', "+
				"rentPrice  ='"+equObj.getRentPrice()+"', "+
				"CountryOfOrigin  ='"+equObj.getCountryOfOrigin()+"', "+
				"productRangeID  ='"+equObj.getRange().getRangeID()+"', "+
				"productDescription  ='"+equObj.getDescription()+"', "+
				"clothingSize  ='"+null+"', "+
				"ClothingColor  ='"+null+"', "+
				"equipmentType   ='"+equObj.getEquipmentType()+"', "+
				"GunFabricator   ='"+null+"', "+
				"GunCalibre  ='"+null+"', "+
				"prodSupID ='"+equObj.getSupplier().getSupplierId()+"' "+
		 	  
		          " WHERE productID = '"+ equObj.getProductId() + "'";
		
		
                System.out.println("Update query:" + query);
  		try{ // update Equipment
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}

	public int insertGun(Gun gun) throws Exception {
		//  Auto-generated method stub
		int nextID = GetMax.getMaxId("Select max(productID) from product");
        nextID = nextID + 1;
        System.out.println("next productID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO product(productID, prodName, purchasePrice, salesPrice, rentPrice, CountryOfOrigin, productRangeID, productDescription, clothingSize, ClothingColor, equipmentType, GunFabricator, GunCalibre, prodSupID)  VALUES('"+
	  		nextID + "','" +
	  		gun.getName()  + "','"  +
	  		gun.getPurchasePrice() + "','"  +
	  		gun.getSalesPrice() + "','"  +
	  		gun.getRentPrice() + "','"  +
	  		gun.getCountryOfOrigin() + "','"  +
	  		gun.getRange().getRangeID() + "','"  +
	  		gun.getDescription() + "','"  +
	  		null + "','"  +
	  		null + "','"  +
	  		null + "','"  +
            gun.getGunFabric() + "','"  +
		    gun.getGunCalibre() + "','"  +
		    gun.getSupplier().getSupplierId() + "')";

       System.out.println("insert : " + query);
      try{ // insert new equipment
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Clothing ikke oprettet");
          throw new Exception ("Clothing is not inserted correct");
       }
       return(rc);
	}

	public int updateGun(Gun gun) {
		//  Auto-generated method stub
		Gun gunObj  = gun;
		int rc=-1;
		
		String query="UPDATE product SET "+
				"prodName ='"+ gunObj.getName()+"', "+
				
				"purchasePrice ='"+gunObj.getPurchasePrice()+"', "+
				"salesPrice ='"+gunObj.getSalesPrice()+"', "+
				"rentPrice  ='"+gunObj.getRentPrice()+"', "+
				"CountryOfOrigin  ='"+gunObj.getCountryOfOrigin()+"', "+
				"productRangeID  ='"+gunObj.getRange().getRangeID()+"', "+
				"productDescription  ='"+gunObj.getDescription()+"', "+
				"clothingSize  ='"+null+"', "+
				"ClothingColor  ='"+null+"', "+
				"equipmentType   ='"+null+"', "+
				"GunFabricator   ='"+gunObj.getGunFabric()+"', "+
				"GunCalibre   ='"+gunObj.getGunCalibre()+"', "+
				"prodSupID ='"+gunObj.getSupplier().getSupplierId()+"' "+
		 	  
		          " WHERE productID = '"+ gunObj.getProductId() + "'";
		
		
                System.out.println("Update query:" + query);
  		try{ // update Gun
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}
	
	
	
	///////////////////
	
	
	public int updateProduct(Product pro) {
		//  Auto-generated method stub
		Product proObj  = pro;
		int rc=-1;
		
		String query="UPDATE product SET "+
				"prodName ='"+ proObj.getName()+"', "+
				
				"purchasePrice ='"+proObj.getPurchasePrice()+"', "+
				"salesPrice ='"+proObj.getSalesPrice()+"', "+
				"rentPrice  ='"+proObj.getRentPrice()+"', "+
				"CountryOfOrigin  ='"+proObj.getCountryOfOrigin()+"', "+
				"productRangeID  ="+null+", "+
				"productDescription  ='"+proObj.getDescription()+"', "+
				"clothingSize  ="+null+", "+
				"ClothingColor  ="+null+", "+
				"equipmentType   ="+null+", "+
				"GunFabricator   ="+null+", "+
				"GunCalibre   ="+null+", "+
				"prodSupID ="+null+" "+
		 	  
		          " WHERE productID = '"+ proObj.getProductId() + "'";
		
		
                System.out.println("Update query:" + query);
  		try{ // update Gun
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}

	
	
	//////////////////////////// update product //////7

	public int deleteProduct(int prodID) {
		//  Auto-generated method stub
		int rc=-1;
		  
	  	String query="DELETE FROM product WHERE productID = '" +
	  			prodID + "'";
                System.out.println(query);
	  	try{ // delete from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in product db: "+ex);
   	        }
		return(rc);
	}

	private Product singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Product proObj = new Product();
        String query =  buildQuery(wClause);
        System.out.println("DbProduct -singleWhere " + query); 
        try{ // read from Product
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       proObj = buildProduct(results);
               //missing the test on retriveassociation
               
           }//end if
	   stmt.close();
	   if(retrieveAssociation)
       {   //The Range and Supplier is to be build as well
		   int rangeid = proObj.getRange().getRangeID();
           DBRange dbRange= new DBRange();
           Range ran = dbRange.findRange(rangeid, false);
           proObj.setRange(ran);
           System.out.println("Range is seleceted");
           int supplierid = proObj.getSupplier().getSupplierId();
           DBSupplier dbSup = new DBSupplier();
           Supplier sup = dbSup.findSupplier(supplierid);
           proObj.setSupplier(sup);
           System.out.println("Supplier selected");
       
	}//slut try	
        else{ //no employee was found
            proObj = null;
        }
        }
	catch(Exception e){
	   System.out.println("Query exception - select product : "+e);
	   e.printStackTrace();
        }
	return proObj;
    }
	
	 //miscWhere is used when more than one Product is selected and build
    private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();
		
		String query =  buildQuery(wClause);
                System.out.println("DbProduct " + query); 
  		try{ // read from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
				Product proObj = new Product();
			 proObj = buildProduct(results);	
                         list.add(proObj);
                         //missing tes on retriveAssociation
			}//end while
			stmt.close();
			if(retrieveAssociation)
            {   //The supervisor and department is to be build as well
                for(Product pObj : list){
                    int rangeid = pObj.getRange().getRangeID();
                    DBRange dbRange= new DBRange();
                    Range ran = dbRange.findRange(rangeid, false);
                    pObj.setRange(ran);
                    System.out.println("Range is seleceted");
                    int supplierid = pObj.getSupplier().getSupplierId();
                    DBSupplier dbSup = new DBSupplier();
                    Supplier sup = dbSup.findSupplier(supplierid);
                    pObj.setSupplier(sup);
                    System.out.println("Supplier selected");
                }
            }//end if   
		
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select product : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private Product buildProduct(ResultSet results)
    {
    	Product proObj = new Product();
    	Range rngObj = new Range();
    	Supplier supObj = new Supplier();
       
         try{
               proObj.setProductId(results.getInt("productID"));
               proObj.setName(results.getString("prodName"));
               proObj.setPurchasePrice(results.getDouble("purchasePrice"));
               proObj.setSalesPrice(results.getDouble("salesPrice"));
               proObj.setRentPrice(results.getDouble("rentPrice"));
               proObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
               rngObj.setRangeID(results.getInt("productRangeID"));
               //System.out.println("Range get" +rngObj.getRangeID());
               proObj.setRange(rngObj);
               proObj.setDescription(results.getString("productDescription"));
/*               cloObj.setClothingSize(results.getString("clothingSize"));
               cloObj.setClothingColor(results.getString("clothingColor"));
               equObj.setEquipmentType(results.getString("equipmentType"));
               gunObj.setGunFabric(results.getString("gunFabricator"));
               gunObj.setGunCalibre(results.getString("gunCalibre"));*/
               supObj.setSupplierId(results.getInt("prodsupid"));
               proObj.setSupplier(supObj);
               
               
          }
         catch(Exception e)
         {
             System.out.println("building product object");
         }
        
        return proObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT productID, prodName, purchasePrice, salesPrice, rentPrice, CountryOfOrigin, productRangeID, productDescription, prodSupID FROM product, range, supplier where productrangeid=rangeid and prodsupid=supplierid "; 
            
           

		if (wClause.length()>0)
			query = query + "  and " + wClause;
			
		return query;
	}
    
    
}
